-- sp_helprestore2 dbname
-----------------------------------
WITH full_backups AS
  (SELECT ROW_NUMBER() OVER(PARTITION BY BS.database_name, BS.type
                            ORDER BY BS.database_name ASC, BS.backup_finish_date DESC) AS [Row Number],
          D.name AS [database_name],
          BS.backup_set_id,
          BS.type AS backup_type,
          BS.backup_finish_date,
          D.recovery_model_desc
   FROM master.sys.databases AS D WITH(NOLOCK)
   LEFT JOIN msdb.dbo.[backupset] AS BS WITH(NOLOCK) ON D.name = BS.database_name /* FILTERING OPTIONS*/ --WHERE BS.[type] = '<backup_type,,D>'
--WHERE BS.[name] = '<database_name,,Foo_DB>'
)
SELECT FB.database_name,
       CASE FB.backup_type
           WHEN 'D' THEN 'Data'
           WHEN 'I' THEN 'Differential'
           WHEN 'L' THEN 'Transaction Log'
       END AS backup_type_desc,
       FB.recovery_model_desc,
       FB.backup_finish_date,
       BMF.physical_device_name,
       DATEDIFF(HOUR, FB.backup_finish_date, GETDATE()) AS backup_hours,
       DATEDIFF(MINUTE, FB.backup_finish_date, GETDATE()) AS backup_minutes
FROM full_backups FB
LEFT JOIN msdb.dbo.[backupset] BS WITH(NOLOCK) ON FB.backup_set_id = BS.backup_set_id
LEFT JOIN msdb.dbo.backupmediafamily BMF WITH(NOLOCK) ON BS.media_set_id = BMF.media_set_id
WHERE FB.[Row Number] = 1
ORDER BY FB.database_name,
         FB.backup_finish_date DESC
